<?php

class Application_Model_PersistenceService
{

	protected $_dbTableQuestion;
	protected $_dbTableAnswer;
	protected $_dbTableDialog;
	protected $_dbTableQuestionAnswers;
	protected $_dbTableConversation;
	protected $_dbTablePresent;
	protected $_dbTableDNA;
	protected $_dbTableDNAAssignment;

	public function __construct(
	Application_Model_DbTable_Question &$dbQuestion = null,
	Application_Model_DbTable_Answer &$dbAnswer = null,
	Application_Model_DbTable_QuestionAnswers &$dbQuestionAnsers = null,
	Application_Model_DbTable_Dialog &$dbDialog = null,
	Application_Model_DbTable_Present &$dbPresent = null,
	Application_Model_DbTable_Conversation &$dbConversation = null,
	Application_Model_DbTable_DNA &$dbDNA = null,
	Application_Model_DbTable_DNAAssignment &$dbDNAAssignment = null) {

		$this->_dbTableQuestion = $dbQuestion instanceof Zend_Db_Table_Abstract ? $dbQuestion : new Application_Model_DbTable_Question();
		$this->_dbTableAnswer = $dbAnswer instanceof Zend_Db_Table_Abstract ? $dbAnswer : new Application_Model_DbTable_Answer();
		$this->_dbTableQuestionAnswers = $dbQuestionAnsers instanceof Zend_Db_Table_Abstract ? $dbQuestionAnsers : new Application_Model_DbTable_QuestionAnswers();
		$this->_dbTableDialog = $dbQuestionAnsers instanceof Zend_Db_Table_Abstract ? $dbDialog : new Application_Model_DbTable_Dialog();
		$this->_dbTableConversation = $dbConversation instanceof Zend_Db_Table_Abstract ? $dbConversation : new Application_Model_DbTable_Conversation();
		$this->_dbTablePresent = $dbPresent instanceof Zend_Db_Table_Abstract ? $dbPresent : new Application_Model_DbTable_Present();
		$this->_dbTableDNA = $dbDNA instanceof Zend_Db_Table_Abstract ? $dbDNA : new Application_Model_DbTable_DNA();
		$this->_dbTableDNAAssignment = $dbDNAAssignment instanceof Zend_Db_Table_Abstract ? $dbDNAAssignment : new Application_Model_DbTable_DNAAssignment();
	}

	public function getQuestion($id) {
		$select = $this->_dbTableQuestionAnswers->select()->setIntegrityCheck(false)
			->from(array('qa' => 'QuestionAnswers'))		
			->joinInner(array('a' => 'Answer'), 'a.id = qa.answer_id', array('answer_text'=>'a.text'))
			->joinInner(array('q' => 'Question'), 'q.id = qa.question_id', array('question_text'=>'q.text'))
			->where('qa.question_id=?', $id);
			 
		$rows = $this->_dbTableQuestionAnswers->fetchAll($select);

		$question = new Application_Model_Question();
		$qInit = false;
		foreach ($rows as $row) {
			if (!$qInit){
				$question->setId($row->question_id);
				$question->setText($row->question_text);
				$qInit = true;
			}
			
			$answer = new Application_Model_Answer();
			$answer->setId($row->answer_id);
			$answer->setText($row->answer_text);
			$question->addAnswer($answer);
		}

		return $question;
	}

	public function fetchRandomQuestion ($conversationId="") {
		$usedQuestions = array();
		if ($conversationId != "") {
			$usedQuestions = $this->fetchConversationQuestions($conversationId);
		}
		
		$select = $this->_dbTableQuestion->select();
		if (count($usedQuestions)>0) {
			$select = $select->where('id not in (?)', $usedQuestions);
		};
		$select = $select->order('RAND(NOW())')->limit(1,0);
		$row = $this->_dbTableDialog->fetchRow($select);
		
		return $this->getQuestion($row->id);
	}

	public function getDNAs () {
		$dnas = array();
		$select = $this->_dbTableDNA->select();
		$rows = $this->_dbTableDNA->fetchAll($select);
		foreach ($rows as $row) {
			$dna = new Application_Model_DNA();
			$dna->setId($row->id);
			$dna->setName($row->name);
			$dnas[] = $dna;
		}
		
		return $dnas;
	}
	
	
	/**
	 * Finds the relevant Presents based on a user {@link Application_Model_FindConversation}.
	 * This implementation will perform the datamining queries.
	 * 
	 * @param int $conversationId Identifier of the {@link Application_Model_FindConversation}
	 * @return array of {@link Application_Model_DbTable_Present} objects
	 */
	public function findRelevantPresents ($conversationId) {
		$candidates = array();
		$candidatesPresent = array();
		$result = array();
		
		// first find a set of candidates of conversation which are 
		// similar (ie mostly the same questions asked)
		$subQryQuestions = $this->_dbTableDialog->select()->from('Dialog', array('question_id'))
			->where('conversation_id = ?', $conversationId);
		$subQryAnswers = $this->_dbTableDialog->select()->from('Dialog', array('answer_id'))
			->where('conversation_id = ?', $conversationId);
			
		$select = $this->_dbTableDialog->select()
			->from(array('d'=>'Dialog'), array('d.conversation_id','SUM((relevant+1)*2) as qcount'))
			->where("conversation_id != ?", $conversationId)
			->where("question_id in ($subQryQuestions)")
			->where("answer_id in ($subQryAnswers)")
			->group('conversation_id')
			->order('qcount DESC')
			->limit(20,0);
			
		$rows = $this->_dbTableDialog->fetchAll($select);
		foreach ($rows as $row) {
			$candidates[] = $row->conversation_id;
		}
		
		// this applies when no question was yet answered
		// or there never was a conversation with the question asked so far
		// so no optimizations possible, return random quest
		if (empty($candidates)) {
			return result;
		}
		
		// look for conversations with have the most equal answers to question
		$select = $this->_dbTableDialog->select()
			->from(array('d1'=>'Dialog'), array('d1.conversation_id', 'SUM((d1.relevant+1)*2) as qcount'))
			->joinInner(array('d2'=>'Dialog'), '(d1.question_id=d2.question_id AND d1.answer_id=d2.answer_id AND d2.conversation_id='.$conversationId.' AND d1.conversation_id<>'.$conversationId.')', array())
			->where("d1.conversation_id IN (?)", $candidates)
			->group('d1.conversation_id')
			->order('qcount DESC')
			->limit(10, 0);
			
		$rows = $this->_dbTableDialog->fetchAll($select);
		foreach ($rows as $row) {
			$candidates[] = $row->conversation_id;
		}
		
		// fetch the presents for the ranked candidates
		// RC means RegisterConversation - FC is FindConversation
		
		$selectFC = $this->_dbTableConversation->select()
			->from(array('cp' => 'ConversationPresents'), array('cp.conversation_id, cp.present_id'))
			->where('cp.conversation_id IN (?)', $candidates);
		$selectRC = $this->_dbTableConversation->select()
			->from(array('c'=>'Conversation'), array('conversation_id'=>'c.id', 'present_id'=>'c.present_id'))
			->where('c.dtype=?', 'RC')
			->where('c.id IN (?)', $candidates);
		
		$select = $this->_dbTablePresent->select()->union(array($selectRC, $selectFC))->distinct();
				
		$rows = $this->_dbTableConversation->fetchAll($selectRC);
		foreach ($rows as $row) {
			$candidatesPresent[$row->conversation_id] = $row->present_id;
		};
		
		$select = $this->_dbTablePresent->select()->where('id IN (?)', array_keys($candidatesPresent));
		
		$tmp = array();
		$rows = $this->_dbTablePresent->fetchAll($select);
		foreach ($rows as $row) {
			$p = new Application_Model_Present();
			$p->setId($row->id);
			$p->setName($row->name);
			$p->setImage($row->image);
			$tmp[$row->id] = $p;
		}
		
		// sort according to candidates´
		// remove duplicates
		$inserted = array();
		foreach ($candidates as $c){
			$present = $tmp[$candidatesPresent[$c]];
			if (is_object($present) && !array_search($present->getId(), $inserted)){
				$result[] = $present;
				$inserted[] = $present->getId();
			}
		}

		return $result;
	}
	
	public function fetchRelevantQuestion ($conversationId) {

		$resultQuestion;
		$candidateConvSame = array();
		$candidateConvRel = array();
		
		// first we look for (canditate) conversation with the most same relevant question answered
		// the result is a map mapping [conversation]=>[amount of same question to given conversation]
		 
		$subselect = $this->_dbTableDialog->select()->from('Dialog', array('question_id'))
			->where('conversation_id = ?', $conversationId);

		$select = $this->_dbTableDialog->select()
			->from(array('d'=>'Dialog'), array('d.conversation_id','COUNT(*) as qcount'))
			->where("conversation_id != ?", $conversationId)
			->where("relevant = 1")
			->where("question_id in ($subselect)")
			->group('conversation_id')
			->order('qcount DESC')
			->limit(20,0);
		
		
		$rows = $this->_dbTableDialog->fetchAll($select);
		foreach ($rows as $row) {
			$candidateConvSame[$row->conversation_id] = $row->qcount;
		}
		
		// this applies when no question was yet answered
		// or there never was a conversation with the question asked so far
		// so no optimizations possible, return random quest
		if (empty($candidateConvSame)) {
			return $this->fetchRandomQuestion($conversationId);
		}
		
		// now querying for the amount of question answered in the canditate conversation
		// and calcute the ...which is sameQuestion/overallQuestionsAnswered in percent
		$select = $this->_dbTableDialog->select()
			->from(array('d'=>'Dialog'), array('d.conversation_id', 'COUNT(*) as qcount'))
			->where("conversation_id IN (?)", array_keys($candidateConvSame))
			->group('conversation_id');
		
		$rows = $this->_dbTableDialog->fetchAll($select);
		foreach ($rows as $row) {
			$candidateConvRel[$row->conversation_id] = ($candidateConvSame[$row->conversation_id]*100) / $row->qcount;
		}
		
		// sot it by relevance, the first entry is the most relevant one
		arsort($candidateConvRel);
		
		// now look if the relevant conversation contains another relevant question
		// which was not yet asked
		$candidateConv = array_keys($candidateConvRel);
		foreach ($candidateConv as $candidate) {
			// now the first entry is the most equal conversation regarding marked relevant question
			$select = $this->_dbTableDialog->select()
				->from(array('d'=>'Dialog'), 'd.question_id')
				->where('d.relevant=1')
				->where("d.question_id not in ($subselect)")
				->where('d.conversation_id = ?', $candidate);
			
			$rows = $this->_dbTableDialog->fetchAll($select);
			foreach ($rows as $row) {
				$resultQuestion = $this->getQuestion($row->question_id);
			}
			
			if (is_object($resultQuestion)) {
				return $resultQuestion;
			}
		}
		
		// if no relevant Question could be calculated, simply use a random one
		return $this->fetchRandomQuestion($conversationId);
		
	}
	
	private function fetchConversationQuestions ($conversationId) {
		$select = $this->_dbTableDialog->select('d.question_id')->setIntegrityCheck(false)->from(array('d'=>'Dialog'))
			->joinInner(array('c' => 'Conversation'), 'c.id = d.conversation_id')
			->where('c.id=?', $conversationId);
			
		$usedQuestions = array();
		
		$rows = $this->_dbTableDialog->fetchAll($select);
		foreach ($rows as $row) {
			$usedQuestions[] = $row->question_id;
		}
		
		return $usedQuestions;
	}
	
	public function saveQuestion(Application_Model_Question &$question) {

		// first save all referenced answers
		foreach ($question->getAnswers() as $answer) {
			$dataAnswer = array(
				'text' => $answer->getText()
			);
			
			if (null === ($id = $answer->getId()) || 0 === $id) {
				$pk = $this->_dbTableAnswer->insert($dataAnswer);
				$answer->setId($pk);
			} else {
				$this->_dbTableAnswer->update($dataAnswer, array('id = ?' => $answer->getId()));
			}
			
			// save dna assignments for each answer
			foreach ($answer->getDnaAssignments() as $dnaAssignment) {
				$dataDnaAssign = array(
					'dna_id' => $dnaAssignment->getDna()->getId(),
					'answer_id' => $answer->getId(),
					'presentee' => $dnaAssignment->getPresentee() ? 1 : 0,
					'amount' => $dnaAssignment->getAmount()
				);
				$this->_dbTableDNAAssignment->insert($dataDnaAssign);
			}
		}
		

		// save the question itself
		$dataQuestion = array(
             'text'   => $question->getText()
             //'created' => date('Y-m-d H:i:s'),
		);
		if (null === ($id = $question->getId()) || 0 === $id) {
			unset($dataQuestion['id']);
			$pk = $this->_dbTableQuestion->insert($dataQuestion);
			$question->setId($pk);
		} else {
			$this->_dbTableQuestion->update($dataQuestion, array('id = ?' => $question->getId()));
		}

	
		// finally save the mapping between question and answers
		foreach ($question->getAnswers() as $answer) {
			$dataQAs = array(
				'question_id' => $question->getId(),
				'answer_id' => $answer->getId()
			);
			
			// check if already exists
			$this->_dbTableQuestionAnswers->insert($dataQAs);
		}

	}

	public function removeQuestion($questionId) {
		// remove in cascadically it here
		$subselect = $this->_dbTableQuestionAnswers->select();
		$subselect->from('QuestionAnswers', 'answer_id')->where('question_id = ?', $questionId);
		
		$this->_dbTableDNAAssignment->delete("answer_id IN ($subselect)");
		
		$this->_dbTableQuestionAnswers->delete(
			$this->_dbTableQuestionAnswers->getAdapter()->quoteInto('question_id = ?', $questionId)
		);
		
		$this->_dbTableQuestion->delete(
			$this->_dbTableQuestionAnswers->getAdapter()->quoteInto('id = ?', $questionId)
		);
		
	}
	
	public function getQuestions() {
		$questions = array();
		$select = $this->_dbTableQuestion->select();
		$rows = $this->_dbTableQuestion->fetchAll($select);	
		
		foreach ($rows as $row) {
			$question = new Application_Model_Question();
			$question->setId($row->id);
			$question->setText($row->text);
			$questions[] = $question;
		}
		
		return $questions;
	}
	
	public function saveDialog(Application_Model_Dialog &$dialog) {
		
		if ($dialog->getAnswer()->getId() === null) {
			throw new InvalidArgumentException("unsaved Answer. No cascade supported here");
		}
		if ($dialog->getQuestion()->getId() === null) {
			throw new InvalidArgumentException("unsaved Question. No cascade supported here");
		}
		if ($dialog->getId()>0) {
			throw new InvalidArgumentException("Dialog already saved");
		}
			
		$dataDialog = array(
            'question_id' => $dialog->getQuestion()->getId(),
			'answer_id' => $dialog->getAnswer()->getId(),
			'relevant' => $dialog->getRelevant(),
			'conversation_id' => $dialog->getConversation()->getId()
		);
		
		$this->_dbTableDialog->insert($dataDialog);		
	}

	public function saveConversation(Application_Model_Conversation &$conversation) {
		$data = array(
			'id' => $conversation->getId(),
		    'timestamp' => $conversation->getTimestamp(),
			'dtype' => 'FC'
		);
		
		if ($conversation instanceof Application_Model_RegisterConversation && $conversation->getPresent()!==null) {
			$data['dtype'] = 'RC';
			$presentName = $conversation->getPresent()->getName();
			if ( $conversation->getPresent()->getId()>0){
				$data['present_id'] = $conversation->getPresent()->getId();
			} else if ($presentName!==null && $presentName!=="") {
				// first look if present already save (name is UNIQUE)
				$presentDB = $this->findPresentByName($presentName);
				if ($presentDB !== null) {
					$data['present_id'] = $presentDB->getId();
					$conversation->getPresent()->setId($presentDB->getId());
				} else {
					// save the present first;
					$dataPresent = array( 'name' => $presentName );
					$pk = $this->_dbTablePresent->insert($dataPresent);
					$conversation->getPresent()->setId($pk);
					$data['present_id'] = $pk;
				}
			}
		}
		
		if (null === ($id = $conversation->getId()) || 0 === $id) {
			$pk = $this->_dbTableConversation->insert($data);
			$conversation->setId($pk);
		} else {
			$this->_dbTableConversation->update($data, array('id = ?' => $conversation->getId()));
		}
	}

	public function findPresentByName($name, $exact = true) {
		$results = array();
		$select = $this->_dbTablePresent->select();
		if ($exact) {
			$select = $select->where('name=?', $name);	
		} else {
			$select = $select->where('name LIKE '.$this->_dbTablePresent->getAdapter()->quote($name.'%'));
		}
		$rows = $this->_dbTablePresent->fetchAll($select);
		
		foreach ($rows as $row) {
			$present = new Application_Model_Present();
			$present->setId($row->id);
			$present->setName($row->name);
			$present->setImage($row->image!="" ? $row : null);
			$results[] = $present;
		}
				
		return $exact ? $present : $results;
	}
	
	public function saveDialogTransfer(Application_Model_DialogTransfer &$dialog) {
		
		$dataDialog = array(
			'question_id' => $dialog->getQuestionId(),
			'answer_id' => $dialog->getAnswerId(),
			'conversation_id' => $dialog->getConversationId(),
			'relevant' => $dialog->getRelevant()
		);
		
		$this->_dbTableDialog->insert($dataDialog);
	}
}

